package com.atguigu.preparedstatement.crud;

import com.atguigu.bean.Customer;
import com.atguigu.util.JDBCUtils;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * 针对 Customers 表的查询操作
 *
 * @author admin
 */
public class CustomersForQuery {

    @Test
    public void testQueryForCustomers() {
        String sql1 = "select id, name, email, birth from customers where id = ?";
        Customer customer1 = queryForCustomers(sql1, 13);
        System.out.println(customer1);

        String sql2 = "select name, birth from customers where name = ?";
        Customer customer2 = queryForCustomers(sql2, "周杰伦");
        System.out.println(customer2);
    }

    /**
     * 针对 customers 表的通用查询操作
     */
    public Customer queryForCustomers(String sql, Object... args) {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 1.获取连接
            connection = JDBCUtils.getConnection();

            // 2.预编译 sql 语句，返回 PreparedStatement 实例
            preparedStatement = connection.prepareStatement(sql);

            // 3.填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            // 4.执行，并返回结果集
            resultSet = preparedStatement.executeQuery();
            // 获取结果集的元数据 ResultSetMetaData
            ResultSetMetaData rsmd = resultSet.getMetaData();
            // 通过 ResultSetMetaData 获取结果集中的列数
            int columnCount = rsmd.getColumnCount();

            // 5.处理结果集
            if (resultSet.next()) {
                Customer customer = new Customer();

                // 处理结果集一行数据中的每一列
                for (int i = 0; i < columnCount; i++) {
                    // 获取列值
                    Object columnValue = resultSet.getObject(i + 1);

                    // 获取每个列的列名
                    String columnName = rsmd.getColumnName(i + 1);

                    // 给 customers 对象指定 columnName 属性，赋值为 columnValue   通过反射
                    Field field = Customer.class.getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(customer, columnValue);
                }
                return customer;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6.关闭资源
            JDBCUtils.closeResource(connection, preparedStatement, resultSet);
        }
        return null;

    }

    @Test
    public void testQuery1() {

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            // 1.获取连接
            connection = JDBCUtils.getConnection();

            // 2.预编译 sql 语句，返回 PreparedStatement 实例
            String sql = "select id, name, email, birth from customers where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            // 3.填充占位符
            preparedStatement.setObject(1, 1);

            // 4.执行，并返回结果集
            resultSet = preparedStatement.executeQuery();

            // 5.处理结果集，判断结果集的下一条是否有数据，如果有数据返回 true，并指针下移；如果返回 false，指针不下移
            if (resultSet.next()) {

                // 获取当前这条数据的各个字段名
                int id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String email = resultSet.getString(3);
                Date birth = resultSet.getDate(4);

                // 方式一：
                // System.out.println("id = " + id + " ,name = " + name + " ,email = " + email + " ,birth = " + birth);

                // 方式二：
                Object data = new Object[]{id, name, email, birth};
                // System.out.println(data);

                // 方式三：将数据封装成一个对象（推荐）
                Customer customer = new Customer(id, name, email, birth);
                System.out.println(customer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 6.关闭资源
            JDBCUtils.closeResource(connection, preparedStatement, resultSet);
        }
    }
}
